﻿Imports DTO
Imports System.Data.OleDb
Public Class MonHocDAO
    Inherits Connect

    Public Function DSTenMonHoc() As List(Of String)
        Dim ds As New List(Of String)
        Dim cn As OleDbConnection
        cn = Me.ConnectionData
        Dim sql As String = "Select TenMonHoc from MonHoc"
        Dim da As OleDbDataAdapter
        Dim dt As New DataTable
        da = New OleDbDataAdapter(sql, cn)
        da.Fill(dt)
        For i As Integer = 0 To dt.Rows.Count - 1
            ds.Add(dt.Rows(i).Item(0).ToString)
        Next
        Return ds
    End Function

    Public Function LayDSMonHoc() As DataTable
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim sql As String
        sql = "select TenMonHoc,QuiDinhSoTietHocLienTiepToiThieu,QuiDinhSoTietHocLienTiepToiDa" & _
        " from MonHoc"
        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter(sql, cn)
        da.Fill(dt)
        Return dt
    End Function

    Public Function LayMaMonHoc(ByVal dto As MonHocDTO) As String
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim sql As String = "select mamonhoc from monhoc where tenmonhoc='" & dto.TenMonHoc & "'"
        Dim cmd As New OleDbCommand(sql, cn)
        Return cmd.ExecuteScalar.ToString
    End Function

    Public Function DSGVPhuTrach(ByVal dto As MonHocDTO) As DataTable
        Dim dt As New DataTable
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData()
        Dim sql As String
        sql = "SELECT HotenGiaovien, tentat, TenLopHoc " & _
        "FROM GiaoVien INNER JOIN (LopHoc INNER JOIN (MonHoc INNER JOIN " & _
 "PhanCong ON MonHoc.MaMonHoc = PhanCong.MaMonHoc) ON LopHoc.MaLopHoc = PhanCong.MaLopHoc) " & _
"ON GiaoVien.MaGiaoVien = PhanCong.MaGiaoVien " & _
"WHERE phancong.mamonhoc='" & dto.MaMonHoc & "'"
        Dim da As New OleDbDataAdapter(sql, cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function

    Public Sub CapNhatMonHoc(ByVal dto As MonHocDTO)
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData()
        Dim sql As String
        sql = "Update MonHoc set TenMonHoc='" & dto.TenMonHoc & "', " & _
"QuiDinhSoTietHocLienTiepToiThieu=" & dto.ToiThieu & ", " & _
"QuiDinhSoTietHocLienTiepToiDa=" & dto.ToiDa & _
" where maMonHoc='" & dto.MaMonHoc & "'"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub

    Public Function MaMonHoc() As String
        Return LayMa("MonHoc", "MaMonHoc", "MH")
    End Function
    Public Sub Them(ByVal dto As MonHocDTO)
         Dim cn As New OleDbConnection
        cn = Me.ConnectionData()
        Dim cmd As OleDbCommand
        Dim sql As String
        'Dim mamonhoc As String = LayMa("MonHoc", "MaMonHoc", "MH")
        sql = "insert into MonHoc(maMonHoc,TenMonHoc,QuiDinhSoTietHocLienTiepToiThieu," & _
        "QuiDinhSoTietHocLienTiepToiDa) values ('" & dto.MaMonHoc & "','" & dto.TenMonHoc & "'," & _
          dto.ToiThieu & "," & dto.ToiDa & ")"
        cmd = New OleDbCommand(sql, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub

    Public Sub Xoa(ByVal dto As MonHocDTO)
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData()
        Dim cmd As OleDbCommand
        Dim sql As String
        sql = "Delete from MonHoc where MaMonhoc='" & dto.MaMonHoc & "'"
        cmd = New OleDbCommand(sql, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub
End Class
